﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Security.Cryptography;

namespace mamnontuoitho7.AppCode
{
    public class Kid
    {
        public ConnectGetData _connectData;
        SqlCommand _cmd;
        String _sql = "";
        General _general;

         public Kid() {        
            _connectData =  new ConnectGetData();
            _cmd = new SqlCommand(_sql, _connectData.GetConnect());
            _general = new General();
        }


         /// <summary>
         /// Get all available kid of school
         /// </summary>
         /// <param name="classID">ID of Class</param>
         /// <returns></returns>
         public DataTable GetAvailableKidsList(String classID)
         {
             SqlConnection con = _connectData.GetConnect();
             string sqlUp = "select * from KidDetailsView where KidVisible = 1 and (ClassID <> @classID or ClassID is null)";

             SqlCommand cmd = new SqlCommand(sqlUp, con);
             cmd.Parameters.AddWithValue("@classID", classID);

             return _connectData.GetDataTable(cmd);

         }


         /// <summary>
         /// Get all available kid of a class
         /// </summary>
         /// <param name="classID">ID of Class</param>
         /// <returns></returns>
         public DataTable GetKidClassList(String classID)
         {
             SqlConnection con = _connectData.GetConnect();
             string sqlUp = "select * from KidDetailsView where KidVisible = 1 and ClassID = @classID";

             SqlCommand cmd = new SqlCommand(sqlUp, con);
             cmd.Parameters.AddWithValue("@classID", classID);

             return _connectData.GetDataTable(cmd);

         }


        /// <summary>
        /// Get all kid of a class based on classID
        /// </summary>
        /// <param name="classID">ID of Class</param>
        /// <returns></returns>
         public DataTable GetClassKids(String classID)
         {
             SqlConnection con = _connectData.GetConnect();
             string sqlUp = "select KidID, (KidLastName +' ' + KidFirstName) as FullName, KidAvatar from Kid where ClassID = @classID and KidVisible = 1";
             SqlCommand cmd = new SqlCommand(sqlUp, con);
             cmd.Parameters.AddWithValue("@classID", classID);

             return _connectData.GetDataTable(cmd);

         }


         /// <summary>
         /// Get all information of a kid based on id
         /// </summary>
         /// <param name="kidID"></param>
         /// <returns></returns>
         public DataRow GetKidInformation(String kidID)
         {

             SqlConnection con = _connectData.GetConnect();
             string sqlUp = "select * from KidDetailsView where KidID = @kidID";
             SqlCommand cmd = new SqlCommand(sqlUp, con);
             cmd.Parameters.AddWithValue("@kidID", kidID);

             return _connectData.GetDataTable(cmd).Rows[0];
         }

        /// <summary>
        /// Get all information of a kid in a definition class
        /// </summary>
        /// <param name="kidID"></param>
        /// <returns></returns>
         public DataRow GetKidInformationOfClass(String kidID, String classID)
         {

             SqlConnection con = _connectData.GetConnect();
             string sqlUp = "select * from KidDetailsView where KidID = @kidID and ClassID = @classID";
             SqlCommand cmd = new SqlCommand(sqlUp, con);
             cmd.Parameters.AddWithValue("@kidID", kidID);
             cmd.Parameters.AddWithValue("@classID", classID);
             DataTable dttKid = _connectData.GetDataTable(cmd);

             if (dttKid.Rows.Count == 1)
             {
                 return dttKid.Rows[0];
             }
             else
             {
                 return null;
             }
         }


         /// <summary>
         /// Get all information of a kid in a definition parent
         /// </summary>
         /// <param name="kidID"></param>
         /// <returns></returns>
         public DataRow GetKidInformationOfParent(String kidID, String memberID)
         {

             SqlConnection con = _connectData.GetConnect();
             string sqlUp = "select * from KidDetailsView where KidID = @kidID and memberID = @memberID";
             SqlCommand cmd = new SqlCommand(sqlUp, con);
             cmd.Parameters.AddWithValue("@kidID", kidID);
             cmd.Parameters.AddWithValue("@memberID", memberID);
             DataTable dttKid = _connectData.GetDataTable(cmd);

             if (dttKid.Rows.Count == 1)
             {
                 return dttKid.Rows[0];
             }
             else
             {
                 return null;
             }
         }

        /// <summary>
         /// Insert information for new Kid
        /// </summary>
        /// <param name="parentID"></param>
        /// <param name="classID"></param>
        /// <param name="kidFirstName"></param>
        /// <param name="kidLastName"></param>
        /// <param name="kidSex"></param>
        /// <param name="birthday"></param>
        /// <param name="mother"></param>
        /// <param name="father"></param>
        /// <returns></returns>
         internal int CreateKid(String parentID, String classID, String kidFirstName, String kidLastName, String kidSex, String birthday, String mother, String father)
         {

             //Check Username is existed or not
             SqlConnection con = _connectData.GetConnect();

             int kidMaxID = -1;

             SqlCommand cmd = new SqlCommand("sp_KidInsert", con);
             cmd.CommandType = CommandType.StoredProcedure;
             cmd.Parameters.Add("@parentID", SqlDbType.NVarChar).Value = parentID;
             cmd.Parameters.Add("@classID", SqlDbType.NVarChar).Value = classID;

             cmd.Parameters.Add("@kidFirstName", SqlDbType.NVarChar).Value = kidFirstName;
             cmd.Parameters.Add("@kidLastName", SqlDbType.NVarChar).Value = kidLastName;
             cmd.Parameters.Add("@kidSex", SqlDbType.NVarChar).Value = kidSex;
             cmd.Parameters.Add("@birthday", SqlDbType.NVarChar).Value = birthday;
             cmd.Parameters.Add("@mother", SqlDbType.NVarChar).Value = mother;
             cmd.Parameters.Add("@father", SqlDbType.NVarChar).Value = father;

             SqlParameter parameterReturnValue = new SqlParameter("kidMaxID", SqlDbType.Int, 4);
             parameterReturnValue.Direction = ParameterDirection.ReturnValue;
             cmd.Parameters.Add(parameterReturnValue);

             con.Open();

             cmd.ExecuteNonQuery();
             kidMaxID = Int32.Parse(cmd.Parameters["kidMaxID"].Value.ToString());
             cmd.Dispose();
             con.Close();

             return kidMaxID;
         }


        /// <summary>
        /// Update new information of kid
        /// </summary>
        /// <param name="kidID"></param>
        /// <param name="kidFirstName"></param>
        /// <param name="kidLastName"></param>
        /// <param name="kidSex"></param>
        /// <param name="kidBirthday"></param>
        /// <param name="mother"></param>
        /// <param name="father"></param>
        /// <param name="kidAvatar"></param>
        /// <returns></returns>
         public bool UpdateKid(String kidID, String kidFirstName, String kidLastName, String kidSex, String kidBirthday, String mother, String father, String kidAvatar, String kidVisible, String classID)
         {
             try
             {
                 SqlConnection con = _connectData.GetConnect();
                     string sqlUp = "update Kid set KidFirstName = @kidFirstName," +
                             " KidLastName = @kidLastName," +
                             " KidSex = @kidSex," +
                             " KidBirthday = @kidBirthday," +
                             " Mother = @mother," +
                             " Father = @father," +
                             " KidAvatar = @kidAvatar," +
                             " KidVisible = @kidVisible," +
                             " ClassID = @classID," +
                             " Updatedate = default" +
                             " where KidID = @kidID ";

                     SqlCommand cmd = new SqlCommand(sqlUp, con);
                     cmd.Parameters.AddWithValue("@kidFirstName", kidFirstName);
                     cmd.Parameters.AddWithValue("@kidLastName", kidLastName);
                     cmd.Parameters.AddWithValue("@kidSex", kidSex);
                     cmd.Parameters.AddWithValue("@kidBirthday", kidBirthday);
                     cmd.Parameters.AddWithValue("@mother", mother);
                     cmd.Parameters.AddWithValue("@father", father);
                     cmd.Parameters.AddWithValue("@kidAvatar", kidAvatar);
                     cmd.Parameters.AddWithValue("@kidVisible", kidVisible);
                     cmd.Parameters.AddWithValue("@classID", classID);
                     cmd.Parameters.AddWithValue("@kidID", kidID);

                     con.Open();
                     cmd.ExecuteNonQuery();
                     cmd.Dispose();
                     con.Close();

             }catch(Exception ex){
                 Console.WriteLine(ex.Message);
                 return false;
             }

             return true;
         }


        /// <summary>
        /// Get Kid name from parent
        /// </summary>
        /// <param name="_parentID"></param>
        /// <returns></returns>
         internal DataTable GetParentKids(string _parentID)
         {

             SqlConnection con = _connectData.GetConnect();
             string sqlUp = "select *, (KidLastName + ' ' + KidFirstName) as FullName from Kid where ParentID = @parentID";
             SqlCommand cmd = new SqlCommand(sqlUp, con);
             cmd.Parameters.AddWithValue("@parentID", _parentID);

             return _connectData.GetDataTable(cmd);
         }



         /// <summary>
         /// Update avatar
         /// </summary>
         /// <param name="maxMemID"></param>
         /// <param name="teacherAvatar"></param>
         internal void UpdateAvatar(int maxKidID, string kidAvatar)
         {
             SqlConnection con = _connectData.GetConnect();
             SqlCommand cmd = new SqlCommand("update Kid set KidAvatar = @avatar where KidID = @kidID", con);
             cmd.Parameters.AddWithValue("@avatar", kidAvatar);
             cmd.Parameters.AddWithValue("@kidID", maxKidID);
             con.Open();
             cmd.ExecuteNonQuery();
             cmd.Dispose();
             con.Close();
         }


        /// <summary>
        /// Update new class for the kids
        /// </summary>
        /// <param name="_classID"></param>
        /// <param name="chosenKidList">KidID string separate by comma</param>
         internal void KidChangeClass(string classID, string chosenKidList)
         {
             SqlConnection con = _connectData.GetConnect();
             string sqlUp = "update Kid set " +
                            " ClassID = @classID" +
                            " where KidID in (" + chosenKidList + ")";

             SqlCommand cmd = new SqlCommand(sqlUp, con);
             cmd.Parameters.AddWithValue("@classID", classID);

             con.Open();
             cmd.ExecuteNonQuery();
             cmd.Dispose();
             con.Close();

         }


        /// <summary>
        /// Update the kids disable
        /// </summary>
        /// <param name="_classID"></param>
        /// <param name="chosenKidList"></param>
         internal void KidDisable(string _classID, string chosenKidList)
         {
             SqlConnection con = _connectData.GetConnect();
             string sqlUp = "update Kid set " +
                            " KidVisible = 0" +
                            " where KidID in (" + chosenKidList + ")";

             SqlCommand cmd = new SqlCommand(sqlUp, con);

             con.Open();
             cmd.ExecuteNonQuery();
             cmd.Dispose();
             con.Close();
         }

         /// <summary>
         /// Set a Kid is visible or not
         /// </summary>
         /// <param name="p"></param>
         public void SetKidVisible(String kidID, String kidVisible)
         {
             SqlConnection con = _connectData.GetConnect();

             string sqlUp = "update kid set KidVisible = @kidVisible " +
                                             " where KidID = @kidID ";

             SqlCommand cmd = new SqlCommand(sqlUp, con);
             cmd.Parameters.AddWithValue("@kidVisible", kidVisible);
             cmd.Parameters.AddWithValue("@kidID", kidID);

             con.Open();
             cmd.ExecuteNonQuery();
             cmd.Dispose();
             con.Close();

         }


        /// <summary>
         /// Get all kid of shool based on kidVisible
        /// </summary>
        /// <param name="kidVisible"></param>
        /// <returns></returns>
         internal DataTable GetKidList(String kidVisible)
         {
             string sql = "select KidID, KidFirstName, KidLastName, KidBirthday, KidVisible, ClassName, FullName, KidAvatar from KidDetailsView where KidVisible = @kidVisible order by KidID DESC";
             SqlCommand cmd = new SqlCommand(sql, _connectData.GetConnect());
             cmd.Parameters.AddWithValue("@kidVisible", kidVisible);
             return _connectData.GetDataTable(cmd);
         }


        /// <summary>
        /// Get all available Kid in a class of teacher
        /// </summary>
        /// <param name="memberID"></param>
        /// <returns></returns>
         internal DataTable GetTeacherKidList(String memberID)
         {
             string sql = "select * from TeacherKidListView where MemberID = @memberID";
             SqlCommand cmd = new SqlCommand(sql, _connectData.GetConnect());
             cmd.Parameters.AddWithValue("@memberID", memberID);
             return _connectData.GetDataTable(cmd);
         }

         /// <summary>
         /// Get all Kid in class of a parent
         /// </summary>
         /// <param name="memberID"></param>
         /// <returns></returns>
         internal DataTable GetParentKidList(String memberID)
         {
             string sql = "select KidID, KidLastName, KidFirstName, KidAvatar from ParentKidListView where MemberID = @memberID and KidID is not null";
             SqlCommand cmd = new SqlCommand(sql, _connectData.GetConnect());
             cmd.Parameters.AddWithValue("@memberID", memberID);
             return _connectData.GetDataTable(cmd);
         }



        /// <summary>
        /// Delete Kid
        /// </summary>
        /// <param name="_kidID"></param>
         internal void DeleteKid(String kidID)
         {
             SqlConnection con = _connectData.GetConnect();

             string sqlUp = "Delete Kid where KidID = @kidID ";

             SqlCommand cmd = new SqlCommand(sqlUp, con);
             cmd.Parameters.AddWithValue("@kidID", kidID);

             con.Open();
             cmd.ExecuteNonQuery();
             cmd.Dispose();
             con.Close();
         }
    }
}